import sqlite3

# 连接数据库
def check_and_fix_database():
    try:
        # 连接到数据库
        conn = sqlite3.connect('yetian.db')
        cursor = conn.cursor()
        
        # 检查news表是否存在
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='news'")
        table_exists = cursor.fetchone() is not None
        
        if table_exists:
            print("news表已存在，检查表结构：")
            # 查看当前表结构
            cursor.execute("PRAGMA table_info(news)")
            columns = cursor.fetchall()
            print("当前表结构:")
            for col in columns:
                print(f"- {col}")
            
            # 检查表中是否有id列
            has_id_column = any(col[1] == 'id' for col in columns)
            
            if not has_id_column:
                print("错误: news表缺少id列")
                # 为了简化修复过程，我们将重命名当前表，并创建新的正确结构的表
                print("开始修复表结构...")
                
                # 重命名当前表
                cursor.execute("ALTER TABLE news RENAME TO news_old")
                print("已将当前表重命名为news_old")
                
                # 创建新的news表，包含所有必需的列
                create_table_sql = '''
                CREATE TABLE IF NOT EXISTS news (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id INTEGER DEFAULT 0,
                    order_num INTEGER DEFAULT 0,
                    title TEXT NOT NULL,
                    content TEXT,
                    url TEXT,
                    face_img TEXT,
                    status INTEGER DEFAULT 1,
                    publish_at TEXT DEFAULT CURRENT_TIMESTAMP,
                    is_del INTEGER DEFAULT 0
                )
                '''
                cursor.execute(create_table_sql)
                print("已创建新的news表，包含正确的列结构")
                
                # 如果旧表有数据，尝试迁移（这里简化处理，实际应用中可能需要更复杂的逻辑）
                cursor.execute("SELECT COUNT(*) FROM news_old")
                old_count = cursor.fetchone()[0]
                if old_count > 0:
                    print(f"发现旧表中有{old_count}条记录，尝试迁移数据...")
                    # 获取旧表的列名
                    cursor.execute("PRAGMA table_info(news_old)")
                    old_columns = [col[1] for col in cursor.fetchall()]
                    
                    # 找出新旧表共有的列
                    new_columns = ['user_id', 'order_num', 'title', 'content', 'url', 'face_img', 'status', 'publish_at', 'is_del']
                    common_columns = [col for col in old_columns if col in new_columns]
                    
                    if common_columns:
                        # 构建INSERT语句
                        source_cols = ', '.join(common_columns)
                        target_cols = ', '.join(common_columns)
                        placeholders = ', '.join(['?'] * len(common_columns))
                        
                        sql = f"INSERT INTO news ({target_cols}) SELECT {source_cols} FROM news_old"
                        cursor.execute(sql)
                        
                        # 检查插入了多少条记录
                        new_count = cursor.rowcount
                        print(f"成功迁移了{new_count}条记录到新表")
                    else:
                        print("没有找到可迁移的共有列，无法迁移数据")
                
        else:
            print("news表不存在，创建新表：")
            # 创建新的news表
            create_table_sql = '''
            CREATE TABLE IF NOT EXISTS news (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER DEFAULT 0,
                order_num INTEGER DEFAULT 0,
                title TEXT NOT NULL,
                content TEXT,
                url TEXT,
                face_img TEXT,
                status INTEGER DEFAULT 1,
                publish_at TEXT DEFAULT CURRENT_TIMESTAMP,
                is_del INTEGER DEFAULT 0
            )
            '''
            cursor.execute(create_table_sql)
            print("已创建新的news表")
            
            # 插入一些测试数据
            test_data = [
                (0, 1, "测试新闻标题1", "这是第一条测试新闻的内容", "https://example.com/news1", None, 1, "2023-10-01 10:00:00", 0),
                (0, 2, "测试新闻标题2", "这是第二条测试新闻的内容", "https://example.com/news2", None, 1, "2023-10-02 11:00:00", 0),
                (0, 3, "测试新闻标题3", "这是第三条测试新闻的内容", "https://example.com/news3", None, 1, "2023-10-03 12:00:00", 0)
            ]
            
            insert_sql = '''
            INSERT INTO news (user_id, order_num, title, content, url, face_img, status, publish_at, is_del)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            '''
            
            cursor.executemany(insert_sql, test_data)
            print("已插入3条测试数据")
        
        # 提交更改
        conn.commit()
        print("数据库检查和修复完成")
        
    except Exception as e:
        print(f"数据库操作出错: {e}")
        # 如果发生错误，回滚事务
        if conn:
            conn.rollback()
    finally:
        # 关闭数据库连接
        if conn:
            conn.close()

# 执行检查和修复
if __name__ == '__main__':
    check_and_fix_database()